﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Helper;
using CallCenter.Data.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
    public class ProductRequestUtility : BaseUtility
    {
        public ProductRequestUtility(DataManager manager)
            : base(manager)
        { }
        private static string _conn;
        DB dbConn;

        public ReturnMessage<ProductRequestViewModel> InsertToProductRequest(ProductRequestViewModel model)
        {

            //_conn = ConfigurationManager.ConnectionStrings["mainConnection"].ToString();
            //dbConn = new DB(_conn);
            var returnMessage = new ReturnMessage<ProductRequestViewModel>();
            model.requestById = 1;
            model.productResourceId = 1;

            if (String.IsNullOrEmpty(model.note))
                model.note = "";

          
            // Check if start day is on weekend, update to next coming Monday if so
            if (model.startDate.HasValue)
            {

                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);
                if (model.daysToFinish > 0)
                    model.expectedDeliveryDate = AddBusinessDays(model.startDate, model.daysToFinish);
                else model.expectedDeliveryDate = model.startDate;

            }
       
            //see if user did not enter any value
            int daysToFinish = 0;
            if (model.daysToFinish == 0)
                daysToFinish = Convert.ToInt32(model.daysToFinish);

            var id = this.Insert(model.companyId, model.requestById, model.productId, model.departmentId, model.projectManagerId,
               model.projectManagerId, model.productResourceId, model.name, model.contractSentDate, model.contractSignedDate, model.depositReceivedDate,
                model.depositAmount, model.expectedDeliveryDate, model.deliveryDate, model.goLiveDate, model.estimatedCost, model.actualAmount,
                model.estimateDate, model.statusId, model.paidInFull, model.leadProgrammerId, model.paidById, model.startDate,
               daysToFinish, model.note, model.taskScheduleComplete);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Record Inserted";
                returnMessage.Status = true;
                int empIdByDefault = 0;
                try
                {
                    #region insert current  user as assigined employee by default
                    DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.UserId);
                     empIdByDefault = Convert.ToInt32(empDr["EmployeeId"]);
                    this.Bridge.CommEmployeeUtility.Insert(id, empIdByDefault, DateTime.Now);
                }
                catch { }
                #endregion

                if (empIdByDefault !=0)
                {
                    if (!string.IsNullOrEmpty(model.CommEmployeeIds))
                    {
                        foreach (var item in model.CommEmployeeIds.Split(','))
                        {
                            int commId = id;
                            int conAllId = Convert.ToInt32(item);
                            this.Bridge.ProdutRequestMemeberUtility.Insert(model.id, conAllId, "");
                        }
                        this.Bridge.ProdutRequestMemeberUtility.Insert(model.id, empIdByDefault, "");
                    }
                    else
                    {
                        this.Bridge.ProdutRequestMemeberUtility.Insert(model.id, empIdByDefault, "");
                    }
                }
              
            }
            //if (model.taskScheduleComplete == true)
            //{
            //    sendMail(model.productId);
            //}
            return returnMessage;
        }
      

        public ReturnMessage<ProductRequestViewModel> UpdateToProductRequest(ProductRequestViewModel model)
        {
            _conn = ConfigurationManager.ConnectionStrings["mainConnection"].ToString();
            dbConn = new DB(_conn);

            if (String.IsNullOrEmpty(model.note))
                model.note = "";


            var returnMessage = new ReturnMessage<ProductRequestViewModel>();
            model.requestById = 1;
            model.productResourceId = 1;


            // Check if start day is on weekend, update to next coming Monday if so
            if (model.startDate.HasValue)
            {

                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);
            }

            if (model.daysToFinish > 0 || ((model.startDate == model.expectedDeliveryDate) && (model.startDate != null && model.expectedDeliveryDate != null)))
                model.expectedDeliveryDate = AddBusinessDays(model.startDate, model.daysToFinish);


            //if (model.expectedDeliveryDate.CompareTo(DateTime.MinValue) == 0)
            //    model.expectedDeliveryDate = AddBusinessDays(model.startDate, model.daysToFinish);

            //if (model.deliveryDate.CompareTo(DateTime.MinValue) == 0)
            //    model.deliveryDate = model.startDate.AddDays(model.daysToFinish);

            //if (model.goLiveDate.CompareTo(DateTime.MinValue) == 0)
            //    model.goLiveDate = model.startDate.AddDays(model.daysToFinish);

            //if (model.estimateDate.CompareTo(DateTime.MinValue) == 0)
            //    model.estimateDate = model.startDate.AddDays(model.daysToFinish);
            //see if user did not enter any value
          

            var id = this.Update(model.companyId, model.requestById, model.productId, model.departmentId, model.productRequestConfirmerId, model.projectManagerId, model.productResourceId, model.name, model.contractSentDate, model.contractSignedDate, model.depositReceivedDate, model.depositAmount, model.expectedDeliveryDate, model.deliveryDate, model.goLiveDate, model.estimatedCost, model.actualAmount, model.estimateDate, model.statusId, model.paidInFull, model.note, model.leadProgrammerId, model.paidById, model.startDate, model.daysToFinish, model.taskScheduleComplete, model.id);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Record Inserted";
                returnMessage.Status = true;
                int empIdByDefault = 0;
                try {
                    #region insert current  user as assigined employee by default
                    DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.UserId);
                    empIdByDefault = Convert.ToInt32(empDr["EmployeeId"]);
                    this.Bridge.CommEmployeeUtility.Insert(id, empIdByDefault, DateTime.Now);
                }
                catch { }

                #endregion

             
                this.Bridge.ProdutRequestMemeberUtility.DeleteProductRequestMemberByProductId(model.id);
                if (empIdByDefault != 0)
                {
                    if (!string.IsNullOrEmpty(model.CommEmployeeIds))
                    {
                        foreach (var item in model.CommEmployeeIds.Split(','))
                        {
                            int commId = id;
                            int conAllId = Convert.ToInt32(item);
                            this.Bridge.ProdutRequestMemeberUtility.Insert(model.id, conAllId, "");
                        }
                    }
                    else
                    {
                        this.Bridge.ProdutRequestMemeberUtility.Insert(model.id, empIdByDefault, "");
                    }
                }
            
            }
            //if (model.taskScheduleComplete == true)
            //{
            //    //sendMail(productId);
            //}                          
            return returnMessage;
        }


        public int Insert( //define placeholders for the values in the table to insert
            int companyClientId,
            int requestedById,
            int productId,
            int departmentId,
            int productRequestConfirmerId,
            int projectManagerId,
            int productResourceId,
            string name,
            Nullable<DateTime> contractSentDate,
             Nullable<DateTime> contractSignedDate,
             Nullable<DateTime> depositReceivedDate,
            decimal depositAmount,
             Nullable<DateTime> expectedDeliveryDate,
             Nullable<DateTime> deliveryDate,
             Nullable<DateTime> goLiveDate,
            decimal estimatedCost,
            decimal actualCost,
             Nullable<DateTime> estimateDate,
            int statusId,
            bool paidInFull,
            int leadProgrammerId,
            int paidById,
             Nullable<DateTime> startDate,
            int daysToFinish,
            string note,
            bool taskScheduleComplete
          )
        {
            int nRtn = -1;

            string sql = "Insert INTO ddlProductRequests " +
                                 "(companyClientId, requestedById, productId, departmentId, productRequestConfirmerId, " +
                                 " projectManagerId, productResourceId, name, contractSentDate, contractSignedDate, depositReceivedDate, " +
                                 " depositAmount, startDate, expectedDeliveryDate, deliveryDate, goLiveDate, estimatedCost, actualCost, " +
                                 " estimateDate, statusId, paidInFull, leadProgrammerId,paidById,daysToFinish,note,taskScheduleComplete) " +
                                 " VALUES(@companyClientId, @requestedById, @productId, @departmentId, @productRequestConfirmerId, " +
                                 " @projectManagerId, @productResourceId, @name, @contractSentDate, @contractSignedDate, @depositReceivedDate, " +
                                 " @depositAmount, @startDate, @expectedDeliveryDate, @deliveryDate, @goLiveDate, @estimatedCost, @actualCost, " +
                                 " @estimateDate, @statusId, @paidInFull,@leadProgrammerId,@paidById ,@daysToFinish, @note,@taskScheduleComplete ) " +
                                 "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyClientId", SqlDbType.Int, 10, companyClientId);
                this.Factory.AddParameter(cmd, "@requestedById", SqlDbType.Int, 10, requestedById);
                this.Factory.AddParameter(cmd, "@productId", SqlDbType.Int, 100, productId);
                this.Factory.AddParameter(cmd, "@departmentId", SqlDbType.Int, 10, departmentId);
                this.Factory.AddParameter(cmd, "@productRequestConfirmerId", SqlDbType.Int, 10, productRequestConfirmerId);
                this.Factory.AddParameter(cmd, "@projectManagerId", SqlDbType.Int, 10, projectManagerId);
                this.Factory.AddParameter(cmd, "@productResourceId", SqlDbType.Int, 100, productResourceId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
              
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@actualCost", SqlDbType.Money, 10, actualCost);
                this.Factory.AddParameter(cmd, "@depositAmount", SqlDbType.Money, 10, depositAmount);
                this.Factory.AddParameter(cmd, "@estimatedCost", SqlDbType.Money, 10, estimatedCost);
                this.Factory.AddParameter(cmd, "@note", SqlDbType.NVarChar, 1000, note);
                this.Factory.AddParameter(cmd, "@paidInFull", SqlDbType.Bit, 7, paidInFull);
                this.Factory.AddParameter(cmd, "@leadProgrammerId", SqlDbType.Int, 10, leadProgrammerId);
                this.Factory.AddParameter(cmd, "@paidById", SqlDbType.Int, 0, paidById);
                this.Factory.AddParameter(cmd, "@daysToFinish", SqlDbType.Int, 0, daysToFinish);
                this.Factory.AddParameter(cmd, "@taskScheduleComplete", SqlDbType.Bit, 0, taskScheduleComplete);

                if ((contractSentDate == null) || (contractSentDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, contractSentDate);


                if ((contractSignedDate == null) || (contractSignedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, contractSignedDate);

                if ((depositReceivedDate == null) || (depositReceivedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, depositReceivedDate);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((deliveryDate == null) || (deliveryDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@deliveryDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@deliveryDate", SqlDbType.DateTime, 10, deliveryDate);

                if ((expectedDeliveryDate == null) || (expectedDeliveryDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@expectedDeliveryDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@expectedDeliveryDate", SqlDbType.DateTime, 10, expectedDeliveryDate);

                if ((goLiveDate == null) || (goLiveDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@goLiveDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@goLiveDate", SqlDbType.DateTime, 10, goLiveDate);

                if ((estimateDate == null) || (estimateDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@estimateDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@estimateDate", SqlDbType.DateTime, 10, estimateDate);
              

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }


            return nRtn; //retruning the data stored in the table
            //_conn = ConfigurationManager.ConnectionStrings["mainConnection"].ToString();
            //dbConn = new DB(_conn);
            //Hashtable ht = new Hashtable();
            //ht.Add("companyClientId", companyClientId);
            //ht.Add("requestedById", requestedById);
            //ht.Add("productId", productId);
            //ht.Add("departmentId", departmentId);
            //ht.Add("productRequestConfirmerId", productRequestConfirmerId);
            //ht.Add("projectManagerId", projectManagerId);
            //ht.Add("name", name);
            //ht.Add("productResourceId", productResourceId);
            //ht.Add("contractSignedDate", contractSignedDate);
            //ht.Add("contractSentDate", contractSentDate);
            //ht.Add("depositReceivedDate", depositReceivedDate);
            //ht.Add("startDate", startDate);
            //ht.Add("deliveryDate", deliveryDate);
            //ht.Add("expectedDeliveryDate", expectedDeliveryDate);
            //ht.Add("goLiveDate", goLiveDate);
            //ht.Add("estimateDate", estimateDate);

            //ht.Add("statusId", statusId);
            //ht.Add("actualCost", actualCost);
            //ht.Add("depositAmount", depositAmount);

            //ht.Add("estimatedCost", estimatedCost);
            //ht.Add("note", note);
            //ht.Add("paidInFull", paidInFull);
            //ht.Add("leadProgrammerId", leadProgrammerId);
            //ht.Add("paidById", paidById);
            //ht.Add("daysToFinish", daysToFinish);
            //ht.Add("taskScheduleComplete", taskScheduleComplete);
            //DataSet HDDList = dbConn.GetDataSetForSP("Sp_InsertProductRequest", ht);
        }

        public int Update( //define placeholders for the values in the table to insert
         int companyClientId,
         int requestedById,
         int productId,
         int departmentId,
         int productRequestConfirmerId,
         int projectManagerId,
         int productResourceId,
         string name,
         Nullable<DateTime> contractSentDate,
         Nullable<DateTime> contractSignedDate,
         Nullable<DateTime> depositReceivedDate,
         decimal depositAmount,
         Nullable<DateTime> expectedDeliveryDate,
         Nullable<DateTime> deliveryDate,
         Nullable<DateTime> goLiveDate,
         decimal estimatedCost,
         decimal actualCost,
         Nullable<DateTime> estimateDate,
         int statusId,
         bool paidInFull,
         string note,
         int leadProgrammerId,
         int paidById,
         Nullable<DateTime> startDate,
         int daysToFinish,
         bool taskScheduleComplete,
           int id
       )
        {

            int nRtn = -1;



            string sql = "UPDATE ddlProductRequests " +
                                 "  SET companyClientId=@companyClientId, requestedById=@requestedById, productId=@productId, departmentId=@departmentId, " +
                                 " productRequestConfirmerId=@productRequestConfirmerId, projectManagerId=@projectManagerId, productResourceId=@productResourceId, " +
                                 " name = @name, contractSentDate=@contractSentDate, contractSignedDate=@contractSignedDate, depositReceivedDate=@depositReceivedDate, " +
                                 " depositAmount=@depositAmount, expectedDeliveryDate=@expectedDeliveryDate, deliveryDate=@deliveryDate, goLiveDate=@goLiveDate, " +
                                 " estimatedCost=@estimatedCost, actualCost=@actualCost, estimateDate=@estimateDate, statusId=@statusId, paidInFull=@paidInFull, " +
                                 " leadProgrammerId=@leadProgrammerId, paidById =@paidById, startDate=@startDate, daysToFinish = @daysToFinish, note=@note," +
                                 " taskScheduleComplete=@taskScheduleComplete WHERE id=@ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@companyClientId", SqlDbType.Int, 10, companyClientId);
                this.Factory.AddParameter(cmd, "@requestedById", SqlDbType.Int, 10, requestedById);
                this.Factory.AddParameter(cmd, "@productId", SqlDbType.Int, 100, productId);
                this.Factory.AddParameter(cmd, "@departmentId", SqlDbType.Int, 10, departmentId);
                this.Factory.AddParameter(cmd, "@productRequestConfirmerId", SqlDbType.Int, 10, productRequestConfirmerId);
                this.Factory.AddParameter(cmd, "@projectManagerId", SqlDbType.Int, 10, projectManagerId);
                this.Factory.AddParameter(cmd, "@productResourceId", SqlDbType.Int, 100, productResourceId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
              
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@actualCost", SqlDbType.Money, 10, actualCost);
                this.Factory.AddParameter(cmd, "@depositAmount", SqlDbType.Money, 10, depositAmount);
                this.Factory.AddParameter(cmd, "@estimatedCost", SqlDbType.Money, 10, estimatedCost);
                this.Factory.AddParameter(cmd, "@note", SqlDbType.NVarChar, 1000, note);
                this.Factory.AddParameter(cmd, "@paidInFull", SqlDbType.Bit, 7, paidInFull);
                this.Factory.AddParameter(cmd, "@leadProgrammerId", SqlDbType.Int, 10, leadProgrammerId);
                this.Factory.AddParameter(cmd, "@paidById", SqlDbType.Int, 0, paidById);
                this.Factory.AddParameter(cmd, "@daysToFinish", SqlDbType.Int, 0, daysToFinish);
                this.Factory.AddParameter(cmd, "@taskScheduleComplete", SqlDbType.Bit, 0, taskScheduleComplete);



                if ((contractSentDate == null) || (contractSentDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, contractSentDate);


                if ((contractSignedDate == null) || (contractSignedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, contractSignedDate);

                if ((depositReceivedDate == null) || (depositReceivedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, depositReceivedDate);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((deliveryDate == null) || (deliveryDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@deliveryDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@deliveryDate", SqlDbType.DateTime, 10, deliveryDate);

                if ((expectedDeliveryDate == null) || (expectedDeliveryDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@expectedDeliveryDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@expectedDeliveryDate", SqlDbType.DateTime, 10, expectedDeliveryDate);

                if ((goLiveDate == null) || (goLiveDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@goLiveDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@goLiveDate", SqlDbType.DateTime, 10, goLiveDate);

                if ((estimateDate == null) || (estimateDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@estimateDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@estimateDate", SqlDbType.DateTime, 10, estimateDate);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }

            //_conn = ConfigurationManager.ConnectionStrings["mainConnection"].ToString();
            //dbConn = new DB(_conn);
            //Hashtable ht = new Hashtable();
            //ht.Add("companyClientId", companyClientId);
            //ht.Add("requestedById", requestedById);
            //ht.Add("productId", productId);
            //ht.Add("departmentId", departmentId);
            //ht.Add("productRequestConfirmerId", productRequestConfirmerId);
            //ht.Add("projectManagerId", projectManagerId);
            //ht.Add("name", name);
            //ht.Add("productResourceId", productResourceId);
            //ht.Add("contractSignedDate", contractSignedDate);
            //ht.Add("contractSentDate", contractSentDate);
            //ht.Add("depositReceivedDate", depositReceivedDate);
            //ht.Add("startDate", startDate);
            //ht.Add("deliveryDate", deliveryDate);
            //ht.Add("expectedDeliveryDate", expectedDeliveryDate);
            //ht.Add("goLiveDate", goLiveDate);
            //ht.Add("estimateDate", estimateDate);
            //ht.Add("statusId", statusId);
            //ht.Add("actualCost", actualCost);
            //ht.Add("depositAmount", depositAmount);
            //ht.Add("estimatedCost", estimatedCost);
            //ht.Add("note", note);
            //ht.Add("paidInFull", paidInFull);
            //ht.Add("leadProgrammerId", leadProgrammerId);
            //ht.Add("paidById", paidById);
            //ht.Add("daysToFinish", daysToFinish);
            //ht.Add("taskScheduleComplete", taskScheduleComplete);
            //ht.Add("id", id);
            //dbConn.GetDataSetForSP("Sp_UpdateddlProductRequest", ht);
            nRtn = id;
           return nRtn; //ret
          //  return 0;
        }

        public List<ProductRequestViewModel> GetCompanyProject(int companyId)
        {
            string sql = "SELECT pr.*,cast(pr.depositAmount as decimal(18,3)) as depositAmount,cast(pr.actualCost as decimal(18,3)) as actualAmount, cast(pr.estimatedCost as decimal(18,3)) as estimatedCost, cp.product, pr.name as prName, c.name as clientCompany, cc.lastName+', '+cc.firstName as [reqName], d.code as depCode," +
                                      "(SELECT TOP 1 lastName+', '+firstName as empName FROM ddlEmployees e INNER JOIN ccContacts " +
                                      " on ccContacts.id = e.contactId WHERE ccContacts.id = pr.projectManagerId) as projectManagerName, " +
                                      " (SELECT TOP 1 ccc.lastname+', '+ccc.firstname as leadDeveloperName FROM ddlEmployees e INNER JOIN " +
                                      " ccContacts ccc On ccc.id = e.contactId WHERE ccc.id = pr.leadprogrammerId) as leadDeveloperName, " +
                                      " (SELECT TOP 1 com.name FROM ddlCompany com INNER JOIN " +
                                      " ddlProductRequests dpr On dpr.paidById = com.id WHERE com.id = pr.paidById) as paidByName, " +
                                      "(SELECT STUFF((SELECT ',' + firstName + ' '+ lastName FROM dbo.ccContacts as C " +
                                      "where C.id in(select contactid from  ddlProductRequestMember where productRequestId = pr.id) FOR XML PATH('')), 1, 1, ''))as CommEmployee, " +
                                       "STUFF((SELECT ',' +  CAST(id AS VARCHAR(MAX)) [text()] " +
                                 " FROM ccContacts  as C " +
                                 " WHERE  C.id in(select contactid from  ddlProductRequestMember where productRequestId = pr.id) " +
                                " FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS CommEmployeeIds," +
                                      " s.description as statusName FROM ddlProductRequests pr " +
                                      " INNER JOIN ddlCompany c ON c.id = pr.companyClientId " +
                                      " LEFT JOIN ccContacts cc ON cc.id = pr.requestedById  " +
                                      " INNER JOIN ddlProducts cp ON cp.id = pr.productId " +
                                      " INNER JOIN ddlStatus s ON s.id = pr.statusId " +
                                      " INNER JOIN ddlDepartments d ON d.id = pr.departmentId  " +
                                      " WHERE pr.companyClientId =" + companyId +
                                      " and pr.statusId <>15 " +
                                      " ORDER BY pr.startDate DESC ";
            return this.Factory.ExecuteObjects<ProductRequestViewModel>(sql);

        }

        public List<ProductRequestViewModel> GetAllProjects()
        {
            string sql = "SELECT pr.*,cast(pr.depositAmount as decimal(18,3)) as depositAmount,cast(pr.actualCost as decimal(18,3)) as actualAmount, cast(pr.estimatedCost as decimal(18,3)) as estimatedCost, cp.product, pr.name as prName, c.name as clientCompany, cc.lastName+', '+cc.firstName as [reqName], c.name as companyname,d.code as depCode," +
                                      "(SELECT TOP 1 lastName+', '+firstName as empName FROM ddlEmployees e INNER JOIN ccContacts " +
                                      " on ccContacts.id = e.contactId WHERE ccContacts.id = pr.projectManagerId) as projectManagerName, " +
                                      " (SELECT TOP 1 ccc.lastname+', '+ccc.firstname as leadDeveloperName FROM ddlEmployees e INNER JOIN " +
                                      " ccContacts ccc On ccc.id = e.contactId WHERE ccc.id = pr.leadprogrammerId) as leadDeveloperName, " +
                                      " (SELECT TOP 1 com.name FROM ddlCompany com INNER JOIN " +
                                      " ddlProductRequests dpr On dpr.paidById = com.id WHERE com.id = pr.paidById) as paidByName, " +
                                      "(SELECT STUFF((SELECT ',' + firstName + ' '+ lastName FROM dbo.ccContacts as C " +
                                      "where C.id in(select contactid from  ddlProductRequestMember where productRequestId = pr.id) FOR XML PATH('')), 1, 1, ''))as CommEmployee, " +
                                       "STUFF((SELECT ',' +  CAST(id AS VARCHAR(MAX)) [text()] " +
                                 " FROM ccContacts  as C " +
                                 " WHERE  C.id in(select contactid from  ddlProductRequestMember where productRequestId = pr.id) " +
                                " FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS CommEmployeeIds," +
                                      " s.description as statusName FROM ddlProductRequests pr " +
                                      " INNER JOIN ddlCompany c ON c.id = pr.companyClientId " +
                                      " LEFT JOIN ccContacts cc ON cc.id = pr.requestedById  " +
                                      " INNER JOIN ddlProducts cp ON cp.id = pr.productId " +
                                      " INNER JOIN ddlStatus s ON s.id = pr.statusId " +
                                      " INNER JOIN ddlDepartments d ON d.id = pr.departmentId  " +
                                   
                                      " and pr.statusId <>15 " +
                                      " ORDER BY pr.startDate DESC ";
            return this.Factory.ExecuteObjects<ProductRequestViewModel>(sql);

        }
        public List<ProductViewModel> GetProduts()
        {
            string sql = "SELECT id, product FROM ddlProducts order by product ";
            return this.Factory.ExecuteObjects<ProductViewModel>(sql);

        }
        public List<DepartmentViewModel> GetDepartments()
        {
            string sql = "SELECT id , description as depCode FROM ddlDepartments order by depCode ";
            return this.Factory.ExecuteObjects<DepartmentViewModel>(sql);

        }
        public List<EmployeeAuthViewModel> GetProjectManagers()
        {
            string sql = "SELECT 0 AS id, 'NONE' as projectManagerName UNION ALL SELECT distinct cc.id as id, cc.lastName + ' ' + cc.firstName  as projectManagerName from ccContacts cc INNER JOIN ddlEmployees e ON e.contactId = cc.id order by projectManagerName";
            return this.Factory.ExecuteObjects<EmployeeAuthViewModel>(sql);

        }
        public List<EmployeeAuthViewModel> GetLeadDevelopers()
        {
            string sql = "SELECT 0 AS id, 'NONE' as leadDeveloperName UNION ALL SELECT distinct cc.id as id, cc.lastName + ' ' + cc.firstName  as leadDeveloperName from ccContacts cc INNER JOIN ddlEmployees e ON e.contactId = cc.id order by leadDeveloperName ";
            return this.Factory.ExecuteObjects<EmployeeAuthViewModel>(sql);

        }
        public List<EmployeeAuthViewModel> GetHeldByContacts()
        {
            string sql = "SELECT 0 AS id, 'NONE' as heldContactName UNION ALL SELECT distinct cc.id as id, cc.lastName + ' ' + cc.firstName  as heldContactName from ccContacts cc INNER JOIN ddlEmployees e ON e.contactId = cc.id order by heldContactName ";
            return this.Factory.ExecuteObjects<EmployeeAuthViewModel>(sql);

        }
        public List<InvoluedEmployeeVM> GetInvoluedEmployees()
        {
            string sql = "	SELECT distinct cc.id as id, cc.lastName + ' ' + cc.firstName   as InvoluedEname from ccContacts cc INNER JOIN ddlEmployees e ON e.contactId = cc.id order by InvoluedEname ";
            return this.Factory.ExecuteObjects<InvoluedEmployeeVM>(sql);

        }

        public DataTable GetEmplNames(int productId)
        {
            string sql = "SELECT projectManagerId, leadProgrammerId FROM ddlProductRequests WHERE id = " + productId;

            return this.Factory.ExecuteTable(sql);

        }

        public DataTable GetProductRequest(int id)
        {
           string sql  = "SELECT * FROM ddlProductRequests WHERE id = " + id;

            return this.Factory.ExecuteTable(sql);

        }


        public DataTable GetAllProductRequest(int companyId)
        {
           string sql  = "SELECT id, name FROM ddlProductRequests WHERE companyClientId = " + companyId;

            return this.Factory.ExecuteTable(sql);

        }

        public List<ProductRequestViewModel> GetAllProductRequests(int companyId)
        {
            //string sql = "SELECT id, name FROM ddlProductRequests WHERE companyClientId = " + companyId;

            string sql = "SELECT pr.id, pr.name FROM ddlProductRequests pr " +
                " INNER JOIN ddlCompany c ON c.id = pr.companyClientId " +
                                      " LEFT JOIN ccContacts cc ON cc.id = pr.requestedById " +
                                       " INNER JOIN ddlProducts cp ON cp.id = pr.productId" +
                                       " INNER JOIN ddlStatus s ON s.id = pr.statusId " +
                                      " INNER JOIN ddlDepartments d ON d.id = pr.departmentId " +
                                       " and pr.statusId <> 15  WHERE pr.companyClientId = " + companyId ;

            return this.Factory.ExecuteObjects<ProductRequestViewModel>(sql);

        }

        public List<PaidByViewModel> PaidBy()
        {

            string sql = "SELECT 0 as id, 'None' as paidByName UNION ALL SELECT id, c.name as paidByName FROM ddlCompany c order by paidByName";
            return this.Factory.ExecuteObjects<PaidByViewModel>(sql);
        }

        public int InsertToProjectUpload(int ProjectId, string URL, string FileName) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "INSERT INTO tbl_ProjectUpload (ProjectId,URL, Name) Values(@ProjectId, @URL, @Name) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ProjectId", SqlDbType.Int, 10, ProjectId);
                this.Factory.AddParameter(cmd, "@URL", SqlDbType.NVarChar, 1000, URL);
                this.Factory.AddParameter(cmd, "@Name", SqlDbType.NVarChar, 50, FileName);
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

        public List<ProjectUploadVM> GetProjectUploadfiles(int ProjectId)
        {
            string sql = "select *, Id as id from tbl_ProjectUpload where IsActive is null and ProjectId = " + ProjectId;

            return this.Factory.ExecuteObjects<ProjectUploadVM>(sql);
        }
        public void DeleteProjectFiles(int projectid, int Id) //delete the row of data using the id passed to the function
        {
            string sql = " update tbl_ProjectUpload set IsActive = 0 where ProjectId = " + projectid + "  and id = " + Id;
            this.Factory.ExecuteNonQuery(sql);

        }


        public DataRow GetAllPDF(int projectId)
        {
            string sql = "SELECT pr.*, pr.name as project, cp.product, c.name as clientCompany, c.StreetOne, " +
                                     " c.StreetTwo, c.city, c.stateId, c.zip, st.state, cc.lastName+', '+cc.firstName as [reqName], d.code as depCode," +
                                     "(SELECT TOP 1 lastName+', '+firstName as empName FROM ddlEmployees e INNER JOIN ccContacts " +
                                     " on ccContacts.id = e.contactId WHERE ccContacts.id = pr.projectManagerId) as projectManagerName, " +
                                     " (SELECT TOP 1 ccc.lastname+', '+ccc.firstname as leadDeveloperName FROM ddlEmployees e INNER JOIN " +
                                     " ccContacts ccc On ccc.id = e.contactId WHERE ccc.id = pr.leadprogrammerId) as leadDeveloperName, " +
                                     " (SELECT TOP 1 com.name FROM ddlCompany com INNER JOIN " +
                                     " ddlProductRequests dpr On dpr.paidById = com.id WHERE com.id = pr.paidById) as paidByName, " +
                                     " s.description as status FROM ddlProductRequests pr " +
                                     "INNER JOIN ddlCompany c ON c.id = pr.companyClientId " +
                                     "LEFT JOIN ccContacts cc ON cc.id = pr.requestedById  " +
                                     "INNER JOIN ddlProducts cp ON cp.id = pr.productId " +
                                     "INNER JOIN ddlStatus s ON s.id = pr.statusId " +
                                     "INNER JOIN ddlDepartments d ON d.id = pr.departmentId " +
                                     "INNER JOIN ddlStates st on st.id = c.stateId " +
                                     " WHERE pr.id = " + projectId;


            return this.Factory.ExecuteRecord(sql);

            //if (dt.Rows.Count == 1)
            //    return dt.Rows[0];
            //else
            //    return null;
        }
        public void Delete(int id) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ddlProductRequests " +
                 "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

        public bool GetTaskScheduleComplete(int productRequestId)
        {
            string sql = "SELECT taskScheduleComplete FROM ddlProductRequests WHERE id=" + productRequestId;
            DataTable dt = this.Factory.ExecuteTable(sql);
            return Convert.ToBoolean(dt.Rows[0]["taskScheduleComplete"].ToString());
        }
        #region get only weekdays from the startdate to enddate
        public double getWeekdaysBetween(DateTime firstDay, DateTime lastDay)
        {
            firstDay = firstDay.Date;
            lastDay = lastDay.Date;
            if (firstDay > lastDay)
                throw new ArgumentException("Incorrect last day " + lastDay);

            TimeSpan span = lastDay - firstDay;
            int businessDays = span.Days + 1;
            int fullWeekCount = businessDays / 7;
            // find out if there are weekends during the time exceedng the full weeks
            if (businessDays > fullWeekCount * 7)
            {
                // we are here to find out if there is a 1-day or 2-days weekend
                // in the time interval remaining after subtracting the complete weeks
                int firstDayOfWeek = firstDay.DayOfWeek == DayOfWeek.Sunday ? 7 : (int)firstDay.DayOfWeek;
                int lastDayOfWeek = lastDay.DayOfWeek == DayOfWeek.Sunday ? 7 : (int)lastDay.DayOfWeek;
                if (lastDayOfWeek < firstDayOfWeek)
                    lastDayOfWeek += 7;
                if (firstDayOfWeek <= 6)
                {
                    if (lastDayOfWeek >= 7)// Both Saturday and Sunday are in the remaining time interval
                        businessDays -= 2;
                    else if (lastDayOfWeek >= 6)// Only Saturday is in the remaining time interval
                        businessDays -= 1;
                }
                else if (firstDayOfWeek <= 7 && lastDayOfWeek >= 7)// Only Sunday is in the remaining time interval
                    businessDays -= 1;
            }

            // subtract the weekends during the full weeks in the interval
            businessDays -= fullWeekCount + fullWeekCount;

            // subtract the number of bank holidays during the time interval
            //foreach (DateTime bankHoliday in bankHolidays)
            //{
            //    DateTime bh = bankHoliday.Date;
            //    if (firstDay <= bh && bh <= lastDay)
            //       --businessDays;
            //}

            return businessDays;
        }
        // function to calculate end date based on start date and duration
        public static DateTime AddBusinessDays(Nullable<DateTime> startDate, int days)
        {
            if (days == 0) return startDate.Value;

            if (startDate.HasValue)
            {
                if (startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                {
                    startDate = startDate.Value.AddDays(2);
                    days -= 1;

                }
                else if (startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                {
                    startDate = startDate.Value.AddDays(1);
                    days -= 1;

                }


                startDate = startDate.Value.AddDays(days / 5 * 7);
                int extraDays = days % 5;

                if ((int)startDate.Value.DayOfWeek + extraDays > 5)
                {
                    extraDays += 2;
                }


                return startDate.Value.AddDays(extraDays);
            }
            return DateTime.Now;


        }

        #endregion

    }

}

